using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace eShop.Utility
{   
    public class DatabaseUtility
    {
        protected DatabaseUtility() { }

        public delegate IList GenerateCollectionFromReader(IDataReader returnData);
        public static void AddParameterToSqlCmd(SqlCommand sqlCmd, string parameterId, SqlDbType sqlType, int parameterSize, ParameterDirection parameterDirection, object parameterValue)
        {
            // Validate Parameter Properties
            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));
            if (parameterId == null)
                throw (new ArgumentNullException("parameterId"));
            if (parameterId.Length == 0)
                throw (new ArgumentOutOfRangeException("parameterId"));
            
            // Add Parameter
            SqlParameter newSqlParam = new SqlParameter();
            newSqlParam.ParameterName = parameterId;
            newSqlParam.SqlDbType = sqlType;
            newSqlParam.Direction = parameterDirection;

            if (parameterSize > 0)
                newSqlParam.Size = parameterSize;

            //if (parameterValue != null)
             newSqlParam.Value = parameterValue;

            sqlCmd.Parameters.Add(newSqlParam);
        }

        public static Object ExecuteScalarCmd(SqlCommand sqlCmd)
        {
            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));

            return ExecuteScalarCmd(sqlCmd, ConnectionStringManager.DefaultDBConnectionString);
        }
        
        public static Object ExecuteScalarCmd(SqlCommand sqlCmd, string connectionString)
        {
            // Validate Command Properties

            if (connectionString == null)
                throw (new ArgumentNullException("connectionString"));

            if ( connectionString.Length == 0)
                throw (new ArgumentOutOfRangeException("connectionString"));

            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));

            Object result = null;

            //The using block causes to close the data connection properly after the command execution.
            using (SqlConnection cn = new SqlConnection(connectionString))
            {
                sqlCmd.Connection = cn;
                cn.Open();
                result = sqlCmd.ExecuteScalar();
            }

            return result;
        }

        public static IList ExecuteReaderCmd(SqlCommand sqlCmd, GenerateCollectionFromReader generateCollectionDelegateObject)
        {
            if (generateCollectionDelegateObject == null)
                throw (new ArgumentNullException("generateCollectionDelegateObject"));

            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));

            return ExecuteReaderCmd(sqlCmd, generateCollectionDelegateObject, ConnectionStringManager.DefaultDBConnectionString);
        }
        
        public static IList ExecuteReaderCmd(SqlCommand sqlCmd, GenerateCollectionFromReader generateCollectionDelegateObject, string connectionString)
        {
            if (connectionString == null)
                throw (new ArgumentNullException("connectionString"));

            if (connectionString.Length == 0)
                throw (new ArgumentOutOfRangeException("connectionString"));

            if ( generateCollectionDelegateObject == null)
                throw (new ArgumentNullException("generateCollectionDelegateObject"));

            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));

            using (SqlConnection cn = new SqlConnection(connectionString))
            {
                sqlCmd.Connection = cn;
                cn.Open();
                //sqlCmd.Prepare();
                IList temp = generateCollectionDelegateObject(sqlCmd.ExecuteReader());
                return (temp);
            }
        }

        public static void SetCommandType(SqlCommand sqlCmd, CommandType cmdType, string cmdText)
        {
            if (cmdText == null)
                throw (new ArgumentNullException("cmdText"));

            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));

            sqlCmd.CommandType = cmdType;
            sqlCmd.CommandText = cmdText;
        }

        public static string FormatXMLForIdArray(System.Collections.Generic.List<int> idsToList)
        {
            //converting the list to xml first
            StringBuilder xmlString = new StringBuilder();
            for (int i = 0; i < idsToList.Count; i++)
            {
                xmlString.AppendFormat("<Id>{0}</Id>", idsToList[i]);
            }

            return xmlString.ToString();
        }

        public static string FormatXMLForIdArray(System.Collections.Generic.List<decimal> idsToList)
        {
            //converting the list to xml first
            StringBuilder xmlString = new StringBuilder();
            for (int i = 0; i < idsToList.Count; i++)
            {
                xmlString.AppendFormat("<Id>{0}</Id>", idsToList[i]);
            }

            return xmlString.ToString();
        }

        public static string FormatXMLForIdArray(System.Collections.Generic.List<SqlInt32> idsToList)
        {
            //converting the list to xml first
            StringBuilder xmlString = new StringBuilder();
            for (int i = 0; i < idsToList.Count; i++)
            {
                xmlString.AppendFormat("<Id>{0}</Id>", idsToList[i]);
            }

            return xmlString.ToString();
        }

        public static string FormatXMLForIdArray(System.Collections.Generic.List<SqlGuid> idsToList)
        {
            //converting the list to xml first
            StringBuilder xmlString = new StringBuilder();
            for (int i = 0; i < idsToList.Count; i++)
            {
                xmlString.AppendFormat("<Id>{0}</Id>", idsToList[i]);
            }

            return xmlString.ToString();
        }
    }
}

